﻿/****** Object:  Table [dbo].[UnitOfWeight]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UnitOfWeight](
	[UnitOfWeightId] [int] IDENTITY(1,1) NOT NULL,
	[Unit] [nvarchar](10) NOT NULL,
	[Description] [nvarchar](256) NOT NULL,
	[ToKgMultiplier] [float] NOT NULL,
 CONSTRAINT [pkUnitOfWeight] PRIMARY KEY CLUSTERED 
(
	[UnitOfWeightId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UnitOfLength]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UnitOfLength](
	[UnitOfLengthId] [int] IDENTITY(1,1) NOT NULL,
	[Unit] [nvarchar](10) NOT NULL,
	[Description] [nvarchar](256) NOT NULL,
	[ToMetersMultiplier] [float] NOT NULL,
 CONSTRAINT [pkUnitOfLength] PRIMARY KEY CLUSTERED 
(
	[UnitOfLengthId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MeasurementType]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MeasurementType](
	[MeasurementTypeId] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](256) NOT NULL,
	[Type] [nchar](1) NOT NULL,
 CONSTRAINT [pkMeasurementType] PRIMARY KEY CLUSTERED 
(
	[MeasurementTypeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Account]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Account](
	[AccountId] [int] IDENTITY(1,1) NOT NULL,
	[Username] [nvarchar](256) NOT NULL,
	[DateOfBirth] [smalldatetime] NOT NULL,
	[UnitOfLengthId] [int] NOT NULL,
	[UnitOfWeightId] [int] NOT NULL,
	[PrimaryKeyIndicator] [nvarchar](2) NULL,
	[SecondaryKeyIndicator] [nvarchar](2) NULL,
	[LocalDateFormat] [nvarchar](10) NOT NULL,
	[PrimaryMeasurementTypeId] [int] NULL,
	[SecondaryMeasurementTypeId] [int] NULL,
 CONSTRAINT [pkAccount] PRIMARY KEY CLUSTERED 
(
	[AccountId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Goal]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Goal](
	[GoalId] [int] IDENTITY(1,1) NOT NULL,
	[AccountId] [int] NOT NULL,
	[CreatedOn] [datetime] NOT NULL,
	[TargetValue] [float] NOT NULL,
	[TargetDate] [datetime] NULL,
	[DecreaseTo] [bit] NOT NULL,
	[AchievedOn] [datetime] NULL,
	[Active] [bit] NOT NULL,
	[MeasurementTypeId] [int] NOT NULL,
 CONSTRAINT [pkGoal] PRIMARY KEY CLUSTERED 
(
	[GoalId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Body]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Body](
	[BodyId] [int] IDENTITY(1,1) NOT NULL,
	[AccountId] [int] NOT NULL,
	[EntryDate] [datetime] NOT NULL,
 CONSTRAINT [pkBody] PRIMARY KEY CLUSTERED 
(
	[BodyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Measurement]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Measurement](
	[MeasurementId] [int] IDENTITY(1,1) NOT NULL,
	[MeasurementTypeId] [int] NOT NULL,
	[BodyId] [int] NOT NULL,
	[Value] [float] NOT NULL,
 CONSTRAINT [pkMeasurement] PRIMARY KEY CLUSTERED 
(
	[MeasurementId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [uqMeasurementTypeIdBodyId] UNIQUE NONCLUSTERED 
(
	[MeasurementTypeId] ASC,
	[BodyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Activity]    Script Date: 01/24/2010 22:44:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Activity](
	[ActivityId] [int] IDENTITY(1,1) NOT NULL,
	[AccountId] [int] NOT NULL,
	[BodyId] [int] NULL,
	[ActivityDate] [datetime] NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Description] [nvarchar](256) NULL,
 CONSTRAINT [pkActivity] PRIMARY KEY CLUSTERED 
(
	[ActivityId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Default [DF__Account__Primary__0E6E26BF]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account] ADD  DEFAULT ('W') FOR [PrimaryKeyIndicator]
GO
/****** Object:  Default [DF__Account__Seconda__0F624AF8]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account] ADD  DEFAULT ('BF') FOR [SecondaryKeyIndicator]
GO
/****** Object:  Default [DF__Account__LocalDa__3F115E1A]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account] ADD  DEFAULT ('dd-MM-yyyy') FOR [LocalDateFormat]
GO
/****** Object:  Check [ckGoalTargetValue]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Goal]  WITH CHECK ADD  CONSTRAINT [ckGoalTargetValue] CHECK  (([TargetValue]>(0)))
GO
ALTER TABLE [dbo].[Goal] CHECK CONSTRAINT [ckGoalTargetValue]
GO
/****** Object:  Check [ck_Type]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[MeasurementType]  WITH CHECK ADD  CONSTRAINT [ck_Type] CHECK  (([Type]='L' OR [Type]='W' OR [Type]='A' OR [Type]='P'))
GO
ALTER TABLE [dbo].[MeasurementType] CHECK CONSTRAINT [ck_Type]
GO
/****** Object:  ForeignKey [fkAccountToUnitOfLength]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [fkAccountToUnitOfLength] FOREIGN KEY([UnitOfLengthId])
REFERENCES [dbo].[UnitOfLength] ([UnitOfLengthId])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [fkAccountToUnitOfLength]
GO
/****** Object:  ForeignKey [fkAccountToUnitOfWeight]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [fkAccountToUnitOfWeight] FOREIGN KEY([UnitOfWeightId])
REFERENCES [dbo].[UnitOfWeight] ([UnitOfWeightId])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [fkAccountToUnitOfWeight]
GO
/****** Object:  ForeignKey [fkPrimaryMeasurementType]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [fkPrimaryMeasurementType] FOREIGN KEY([PrimaryMeasurementTypeId])
REFERENCES [dbo].[MeasurementType] ([MeasurementTypeId])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [fkPrimaryMeasurementType]
GO
/****** Object:  ForeignKey [fkSecondaryMeasurementType]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [fkSecondaryMeasurementType] FOREIGN KEY([SecondaryMeasurementTypeId])
REFERENCES [dbo].[MeasurementType] ([MeasurementTypeId])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [fkSecondaryMeasurementType]
GO
/****** Object:  ForeignKey [fkActivityToAccount]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Activity]  WITH CHECK ADD  CONSTRAINT [fkActivityToAccount] FOREIGN KEY([AccountId])
REFERENCES [dbo].[Account] ([AccountId])
GO
ALTER TABLE [dbo].[Activity] CHECK CONSTRAINT [fkActivityToAccount]
GO
/****** Object:  ForeignKey [fkOptionalActivityToBody]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Activity]  WITH CHECK ADD  CONSTRAINT [fkOptionalActivityToBody] FOREIGN KEY([BodyId])
REFERENCES [dbo].[Body] ([BodyId])
GO
ALTER TABLE [dbo].[Activity] CHECK CONSTRAINT [fkOptionalActivityToBody]
GO
/****** Object:  ForeignKey [fkBodyToAccount]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Body]  WITH CHECK ADD  CONSTRAINT [fkBodyToAccount] FOREIGN KEY([AccountId])
REFERENCES [dbo].[Account] ([AccountId])
GO
ALTER TABLE [dbo].[Body] CHECK CONSTRAINT [fkBodyToAccount]
GO
/****** Object:  ForeignKey [fk_GoalMeasurementType]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Goal]  WITH CHECK ADD  CONSTRAINT [fk_GoalMeasurementType] FOREIGN KEY([MeasurementTypeId])
REFERENCES [dbo].[MeasurementType] ([MeasurementTypeId])
GO
ALTER TABLE [dbo].[Goal] CHECK CONSTRAINT [fk_GoalMeasurementType]
GO
/****** Object:  ForeignKey [fkGoalToAccount]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Goal]  WITH CHECK ADD  CONSTRAINT [fkGoalToAccount] FOREIGN KEY([AccountId])
REFERENCES [dbo].[Account] ([AccountId])
GO
ALTER TABLE [dbo].[Goal] CHECK CONSTRAINT [fkGoalToAccount]
GO
/****** Object:  ForeignKey [fkMeasurementToBody]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [fkMeasurementToBody] FOREIGN KEY([BodyId])
REFERENCES [dbo].[Body] ([BodyId])
GO
ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [fkMeasurementToBody]
GO
/****** Object:  ForeignKey [fkMeasurementToMeasurementType]    Script Date: 01/24/2010 22:44:55 ******/
ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [fkMeasurementToMeasurementType] FOREIGN KEY([MeasurementTypeId])
REFERENCES [dbo].[MeasurementType] ([MeasurementTypeId])
GO
ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [fkMeasurementToMeasurementType]
GO
insert into UnitOfLength (Unit, Description, ToMetersMultiplier) values('m', 'Meters', 1)
insert into UnitOfLength (Unit, Description, ToMetersMultiplier) values('cm', 'Centimeters', 0.01)
insert into UnitOfLength (Unit, Description, ToMetersMultiplier) values('"', 'Inches', 0.0254)

insert into UnitOfWeight (Unit, Description, ToKgMultiplier) values('kg', 'Kilogrammes', 1)
insert into UnitOfWeight (Unit, Description, ToKgMultiplier) values('lbs', 'Pounds', 0.45359237)
go
insert into MeasurementType (Description, Type) values('Weight', 'W')
insert into MeasurementType (Description, Type) values('Body Fat %', 'P')
insert into MeasurementType (Description, Type) values('BMI', 'A')
insert into MeasurementType (Description, Type) values('Waist' ,'L')
insert into MeasurementType (Description, Type) values('Chest', 'L')
insert into MeasurementType (Description, Type) values('Arms', 'L')
insert into MeasurementType (Description, Type) values('Thighs', 'L')
insert into MeasurementType (Description, Type) values('Height', 'L')
go
exec dbo.aspnet_Roles_CreateRole @ApplicationName=N'/',@RoleName=N'User'
go
alter table MeasurementType add DisplayOrder int null
alter table MeasurementType add AccountId int null
alter table MeasurementType add constraint fk_MeasurementTypeToAccount foreign key (AccountId) references Account (AccountId)
go
update MeasurementType set DisplayOrder=1 where Description='Weight'
update MeasurementType set DisplayOrder=2 where Description='Body Fat %'
update MeasurementType set DisplayOrder=3 where Description='BMI'
update MeasurementType set DisplayOrder=4 where Description='Waist'
update MeasurementType set DisplayOrder=5 where Description='Chest'
update MeasurementType set DisplayOrder=6 where Description='Arms'
update MeasurementType set DisplayOrder=7 where Description='Thighs'
update MeasurementType set DisplayOrder=8 where Description='Height'
go
alter table MeasurementType alter column DisplayOrder int not null
go 
exec dbo.aspnet_Roles_CreateRole @ApplicationName=N'/',@RoleName=N'Administrator'
go
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'ErrorLog')
	BEGIN
		DROP  Table ErrorLog
	END
GO
CREATE TABLE [dbo].[ErrorLog] (
    [Id] [int] IDENTITY (1, 1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [nvarchar] (255) NOT NULL,
    [Level] [nvarchar] (50) NOT NULL,
    [Logger] [nvarchar] (255) NOT NULL,
    [Message] [nvarchar] (4000) NOT NULL,
    [Exception] [nvarchar] (2000) NULL
    
    constraint pk_Log primary key (Id)
)
GO
  